USE ACVSCore

/********************************************************
	START AND END DATE TIME
********************************************************/

DECLARE @StartDate AS DATETIME = DATEADD(DAY, -1, GETUTCDATE() ) --'2023-07-16 00:00:00'
DECLARE @EndDate AS DATETIME = DATEADD(HOUR, 0 , GETUTCDATE() )--'2024-04-01 00:00:00'


IF OBJECT_ID('tempdb..#Summary') IS NOT NULL
	DROP TABLE #Summary

CREATE TABLE #Summary(
start_date DATETIME,		--'Start Date' 
end_date DATETIME,			--'End Date' 
server_name VARCHAR(255),	--'Server name' 
server_ip VARCHAR(255),		--'Server IP' 
avg_events_ps int,			--'Average number of events per second' 
med_events_ps int,			--'Median number of events per second' 
peak_events_ps int,			--'Peak number of events per second' 
numbers_panels int,			--'Number of panels'
numbers_readers int,		--'Number of readers'
numbers_inputs int,			--'Number of inputs'
number_outputs int,			--'Number of outputs'
number_personel int,		--'Number of Personnel'
number_badges int			--'Number of Badges'
)


/********************************************************

  COUNT OF EVENTS PER SECOND IN THE SPECIFIED TIMEFRAME

*********************************************************/

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
	DROP TABLE #Temp

CREATE TABLE #Temp(
Event_Date DATE,
Hrs int,
Mins int,
Secs int,
Total_Entries int)

DECLARE @dbNames TABLE ([ObjectID] int, [DbName] varchar(255))

/**************************************

Determine the JournalVolume

**************************************/

INSERT INTO @dbNames ([ObjectID], [DbName])
SELECT [ObjectID], [DbName] FROM [ACVSCore].[dbo].[ACVSUJournalVolumeManagement]
WHERE @StartDate <= StartServerUTC 
OR @EndDate <= COALESCE([EndServerUTC], GETUTCDATE())

DECLARE @dbid int
SELECT @dbid = min( [ObjectID] ) FROM @dbNames

/*****************************************

Get events for each Journal Database 

*****************************************/

WHILE @dbid IS NOT NULL
BEGIN
	DECLARE @DatabaseName NVARCHAR(MAX)
	SELECT @DatabaseName = [DbName] FROM @dbNames WHERE [ObjectID] = @dbid

	DECLARE @SQLQuery AS NVARCHAR(MAX)
	SET @SQLQuery = N'USE ' + QUOTENAME(@DatabaseName) +'
	INSERT INTO #Temp 
	SELECT  
			CAST(MessageUTC as DATE) as Event_Date,
			DATEPART(HOUR,MessageUTC) as Hrs, 
			DATEPART(MINUTE, MessageUTC) as Mins,
			DATEPART(SECOND, MessageUTC) as Secs, 
			COUNT(Timestamp) as Total_Entries 
	FROM [ACVSUJournalLog]
	WHERE MessageUTC BETWEEN ' + char(39) + CONVERT(VARCHAR(20),  @StartDate , 120) +  char(39) 
	+' AND '+  char(39) + CONVERT(VARCHAR(20),  @EndDate , 120) +  char(39) +' 
	GROUP BY
			CAST(MessageUTC as DATE),
			DATEPART(HOUR,MessageUTC ), 
			DATEPART(MINUTE,MessageUTC ),
			DATEPART(SECOND,MessageUTC )';

	EXECUTE(@SQLQuery);

	SELECT @dbid = min( [ObjectID] ) FROM @dbNames WHERE [ObjectID] > @dbid
END

SELECT CAST(CAST(Event_Date AS VARCHAR(20)) + ' ' + CAST(Hrs AS CHAR(2)) + ':' + CAST(Mins AS CHAR(2)) + ':' + CAST(Secs AS CHAR(2)) AS DATETIME) AS Event_Date_Time,  
	Total_Entries
FROM #Temp t
ORDER by 1

/********************************************************

  USING THE SAME DATA SET, GET:
  -MAX AND MIN ENTRIES PER HOUR, 
  -MAX AND MIN ENTRIES PER MINUTE
  -MAX AND MIN ENTRIES PER SECOND 
  FOR EACH DAY IN THE SPECIFIED TIMEFRAME

*********************************************************/

IF OBJECT_ID('tempdb..#TotalsPerHour') IS NOT NULL
	DROP TABLE #TotalsPerHour
IF OBJECT_ID('tempdb..#TotalsPerMinute') IS NOT NULL
	DROP TABLE #TotalsPerMinute
IF OBJECT_ID('tempdb..#TotalsPerSecond') IS NOT NULL
	DROP TABLE #TotalsPerSecond

SELECT Event_Date, MAX(A.TotalEntries) AS Max_Per_Hour, MIN(A.TotalEntries) AS Min_Per_Hour
INTO #TotalsPerHour
FROM
	(SELECT Event_Date, Hrs, SUM(Total_Entries) AS TotalEntries
	 FROM #Temp
	 GROUP BY Event_Date, Hrs) AS A
GROUP BY Event_Date, Hrs

SELECT Event_Date, MAX(A.TotalEntries) AS Max_Per_Minute, MIN(A.TotalEntries) AS Min_Per_Minute
INTO #TotalsPerMinute
FROM
	(SELECT Event_Date, Hrs, Mins, SUM(Total_Entries) AS TotalEntries
	 FROM #Temp
	 GROUP BY Event_Date, Hrs, Mins) AS A
GROUP BY Event_Date, Hrs, Mins

SELECT Event_Date, MAX(A.TotalEntries) AS Max_Per_Second, MIN(A.TotalEntries) AS Min_Per_Second
INTO #TotalsPerSecond
FROM
	(SELECT Event_Date, Hrs, Mins, Secs, SUM(Total_Entries) as TotalEntries
	 FROM #Temp
	 GROUP BY Event_Date, Hrs, Mins, Secs) as A
GROUP BY Event_Date, Hrs, Mins, Secs


/********************************************

BUILD SUMMARY TEMP TABLE

*********************************************/

INSERT INTO #Summary (start_date, end_date, server_name, server_ip)
SELECT  
		@StartDate,
		@EndDate,
		[MachineName] ,
		[IPAddress]
  FROM [ACVSCore].[dbo].[ApplicationServer]	

UPDATE #Summary SET peak_events_ps = (SELECT MAX(Max_Per_Second) FROM #TotalsPerSecond), 
					avg_events_ps = (SELECT AVG(Max_Per_Second) FROM #TotalsPerSecond),
					med_events_ps = (SELECT(
									 (SELECT MAX(Max_Per_Second) FROM
									   (SELECT TOP 50 PERCENT Max_Per_Second FROM #TotalsPerSecond ORDER BY Max_Per_Second) AS BottomHalf)
									 +
									 (SELECT MIN(Max_Per_Second) FROM
									   (SELECT TOP 50 PERCENT Max_Per_Second FROM #TotalsPerSecond ORDER BY Max_Per_Second DESC) AS TopHalf)
									) / 2 AS Median)

SELECT DISTINCT t.Event_Date, th.Max_Per_Hour, th.Min_Per_Hour, tm.Max_Per_Minute, tm.Min_Per_Minute, 
ts.Max_Per_Second--ts.Min_Per_Second // REMOVED , USUALY THISE VALUES IS 1
FROM #Temp t
LEFT OUTER JOIN #TotalsPerHour th on t.Event_Date = th.Event_Date
LEFT OUTER JOIN #TotalsPerMinute tm on t.Event_Date = tm.Event_Date
LEFT OUTER JOIN #TotalsPerSecond ts on t.Event_Date = ts.Event_Date
ORDER BY t.Event_Date


/********************************************************

  COUNT OF ALL ACTIVE BADGES IN THE SYSTEM

*********************************************************/
IF OBJECT_ID('tempdb..#Badges') IS NOT NULL
	DROP TABLE #Badges

SELECT 
	(SELECT COUNT(*) FROM [Access].[Personnel] WHERE ClassType != 'SoftwareHouse.NextGen.Common.SecurityObjects.HiddenPersonnel') as Total_Employee_Records,
	SUM(CASE WHEN UPPER(bt.Name) = 'EMPLOYEE' THEN 1 ELSE 0 END) AS Total_Employee_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'CONTRACTOR' THEN 1 ELSE 0 END) AS Total_Contractor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'VISITOR' THEN 1 ELSE 0 END) AS Total_Visitor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'NONE' THEN 1 ELSE 0 END) AS Total_None_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'EMPLOYEE' AND UPPER(b.Disabled) = 0 THEN 1 ELSE 0 END) AS Active_Employee_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'CONTRACTOR' AND UPPER(b.Disabled) = 0  THEN 1 ELSE 0 END) AS Active_Contractor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'VISITOR' AND UPPER(b.Disabled) = 0  THEN 1 ELSE 0 END) AS Active_Visitor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'NONE' AND UPPER(b.Disabled) = 0  THEN 1 ELSE 0 END) AS Active_None_Badges
INTO #Badges
FROM [Access].[Personnel] e
INNER JOIN [Access].Credential b ON e.ObjectID = b.PersonnelId
INNER JOIN [Access].[PersonnelType] bt ON e.[PersonnelTypeID] = bt.ObjectID

SELECT * FROM #Badges

UPDATE #Summary SET number_personel =
(SELECT COUNT(*) FROM [Access].[Personnel])

UPDATE #Summary SET number_badges =
(SELECT Total_Employee_Records FROM #Badges)

/********************************************************
  
  COUNT OF PANELS

*********************************************************/

UPDATE #Summary SET numbers_panels =
(SELECT SUM(data1) AS 'Panel Count' 
	 FROM 
	 (
			SELECT COUNT(*)  as data1 FROM [Access].[APCController] 
			UNION 
			SELECT COUNT(*)  as data1 FROM [Access].[ISCController] 
			UNION 
			SELECT COUNT(*)  as data1 FROM [Access].[iStarController] 
	 ) a
)

/********************************************************

  COUNT OF READERS

*********************************************************/
UPDATE #Summary SET numbers_readers = 
	(SELECT COUNT(*) as Total_Readers
	FROM [Access].[Reader])

/********************************************************

  COUNT OF OUTPUTS

*********************************************************/
UPDATE #Summary SET number_outputs =
	(SELECT COUNT(*) AS Output_Pin_Count
	FROM [Access].[Output]
	)
/********************************************************

  COUNT OF INPUTS

*********************************************************/
UPDATE #Summary SET numbers_inputs =
(	SELECT COUNT(*) AS Input_Pin_Count
	FROM [Access].[Input]
	)


/********************************************************
SHOW SUMARY
********************************************************/

SELECT 
	start_date AS	'Start Date', 
	end_date AS 'End Date', 
	server_name AS 'Server name' ,
	server_ip AS 'Server IP' ,
	avg_events_ps AS 'Average number of events per second', 
	med_events_ps AS 'Median number of events per second' ,
	peak_events_ps AS 'Peak number of events per second' ,
	numbers_panels AS 'Number of panels',
	numbers_readers AS 'Number of readers',
	numbers_inputs AS 'Number of inputs',
	number_outputs AS 'Number of outputs',
	number_personel AS 'Number of Personnel',
	number_badges AS 'Number of Badges'
FROM #Summary